package org.cancan.demo.execl;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;
 
import java.io.FileOutputStream;
 
public class JsonToExcelExporter {
 
    public static void main(String[] args) {
 
        // 示例JSON数据
        String jsonString = "[{\"name\":\"Alice\",\"age\":25,\"city\":\"New York\"},{\"name\":\"Bob\",\"age\":30,\"city\":\"London\"}]";
        JSONArray jsonArray = new JSONArray(jsonString);
 
        try {
 
            exportJsonToExcel(jsonArray, "D:/output-" + String.valueOf(System.currentTimeMillis()) + ".xlsx");
            System.out.println("json转excel成功");
 
        } catch (Exception e) {
 
            e.printStackTrace();
        }
    }
 
    public static void exportJsonToExcel(JSONArray jsonArray, String filePath) throws Exception {
 
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
 
        // 创建表头
        Row headerRow = sheet.createRow(0);
        JSONObject firstObject = jsonArray.getJSONObject(0);
        int columnIndex = 0;
 
        for (String key : firstObject.keySet()) {
 
            Cell cell = headerRow.createCell(columnIndex++);
            cell.setCellValue(key);
        }
 
        // 填充数据
        int rowIndex = 1;
 
        for (int i = 0; i < jsonArray.length(); i++) {
 
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            Row dataRow = sheet.createRow(rowIndex++);
            columnIndex = 0;
 
            for (String key : jsonObject.keySet()) {
 
                Cell cell = dataRow.createCell(columnIndex++);
 
                Object keyObject = jsonObject.get(key);
 
                // 判断jsonObject中的类型
                if (keyObject instanceof Integer) {
 
                    cell.setCellValue(jsonObject.getInt(key));
 
                } else if (keyObject instanceof String) {
 
                    cell.setCellValue(jsonObject.getString(key));
 
                } else if (keyObject instanceof JSONArray) {
 
                    cell.setCellValue(jsonObject.getJSONArray(key).toString());
 
                } else if (keyObject instanceof JSONObject) {
 
                    cell.setCellValue(jsonObject.getJSONObject(key).toString());
                }
            }
        }
 
        // 调整列宽
        for (int i = 0; i < firstObject.length(); i++) {
 
            sheet.autoSizeColumn(i);
        }
 
        // 保存Excel文件
        FileOutputStream fileOut = new FileOutputStream(filePath);
        workbook.write(fileOut);
        fileOut.close();
 
        workbook.close();
    }
}